LookerでのUNIONの実装方法を色々検証してみた #looker

さがらです。

LookerでUNIONを使用したい場合、基本的にはSQL RunnerでUNIONのクエリを書いて、その内容をLookMLのderived_tableパラメータとして出力する流れかと思います。SQL派生テーブルを使う方法ですね。

なのですが、ふと「すでにLookMLで定義したviewをUNIONしたい場合はどうすればいいんだっけ…？」と感じたこともあり、view関係で色々検証してみました。

その検証結果を本エントリでまとめてみます。

検証結果まとめ

まず、検証した結果のまとめから行きます。

• dimensionパラメータやmeasureパラメータで定義されたフィールドを用いたUNIONは不可
• 派生テーブルを用いたUNIONならば、参照元のテーブルにないフィールドを派生テーブルで定義していてもUNION可能
• 派生テーブルを使わずともsql_table_nameパラメータでUNIONは可能
  • ※ただし、sql_table_nameでのUNIONは基本的に非推奨です。

以下、それぞれの内容について詳細に説明します。

dimensionパラメータやmeasureパラメータで定義されたフィールドを用いたUNIONは不可

これは「当たり前でしょ！」と思われる方もいるかもしれませんが、改めて説明していきます。

例えば、下記のようなviewを定義していたとします。

view: users_union_no1 { # users_union_no2も同じ内容で作り、UNIONさせます
  sql_table_name: "PUBLIC"."USERS" ;;

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}."ID" ;;
  }

  dimension: city {
    type: string
    sql: ${TABLE}."CITY" ;;
  }

  dimension: id_and_city {
    type: string
    sql: ${id} || ${city};;
  }

  measure: count {
    type: count
  }

}

このviewを用いて、以下のようなUNION処理を記述した派生テーブルを動かしてみると、エラーになります。

view: users_union {
  derived_table: {
    sql:
      SELECT id,id_and_city FROM ${users_union_no1.SQL_TABLE_NAME}
      UNION ALL
      SELECT id,id_and_city FROM ${users_union_no2.SQL_TABLE_NAME}
    ;;
  }

  dimension: id {
    type: number
  }

  dimension: id_and_city {
    type: string
  }

}

なぜエラーとなるのか、その理由は${LookMLで定義された一般的なview名.SQL_TABLE_NAME}が参照するのは、参照先のviewファイルで定義されたsql_table_nameパラメータだからです。

つまり、元のテーブルに存在しないフィールドはSELECTする方法がないんですね。

実際に発行されているクエリを見ると、FROM句には「"PUBLIC"."USERS"」が入っていますね。これは元のviewのsql_table_nameパラメータの値そのものです。

派生テーブルを用いたUNIONならば、参照元のテーブルにないフィールドを派生テーブルで定義していてもUNION可能

一方で、Looker内で独自のフィールドを定義して、そのフィールドが存在するviewをUNIONさせたい場合はどうするのか？

これは、派生テーブルならば可能です。実際の例を用いて説明していきます。

先程の説明で使用したusers_union_no1を、派生テーブルを使って書いてみると下記のようになります。

view: users_union_no1 { # users_union_no2も同じ内容で作り、UNIONさせます

  derived_table: {
    sql:
      SELECT
        id
        ,city
        ,id || city AS id_and_city
        ,count(*)
      FROM
        "PUBLIC"."USERS"
      GROUP BY
        id
        ,city
        ,id || city
    ;;
  }
}

こちらの派生テーブルを使って、以下のようにUNION処理を記述した派生テーブルを動かしてみると、今度は問題なく動くことが確認できます。

view: users_union {
  derived_table: {
    sql:
      SELECT id,id_and_city FROM ${users_union_no1.SQL_TABLE_NAME}
      UNION ALL
      SELECT id,id_and_city FROM ${users_union_no2.SQL_TABLE_NAME}
    ;;
  }

  dimension: id {
    type: number
  }

  dimension: id_and_city {
    type: string
  }

}

なぜ、派生テーブルだと上手くいくのか？それは、${派生テーブルで定義されたview名.SQL_TABLE_NAME}が参照するのは、派生テーブルのsqlパラメータに記述されたクエリが返す結果を持つ一時的なテーブルだからです。

つまり、元のテーブルにないフィールドであろうが、派生テーブルで定義されたフィールドならば参照できてしまうんですね。

実際に発行されているクエリを見ると、WITH句でUNIONに使用している派生テーブルの内容が定義されているのがわかるかと思います。

SQL派生テーブルを使わずともsql_table_nameパラメータでUNIONは可能　※非推奨事項

まず、下記のようなUNIONを行うSQL派生テーブルがあったとします。

view: users_union {
  derived_table: {
    sql:
      SELECT id FROM ${users_union_no1.SQL_TABLE_NAME}
      UNION ALL
      SELECT id FROM ${users_union_no2.SQL_TABLE_NAME}
    ;;
  }

  dimension: id {
    type: number
  }
}

このsqlパラメータ内に書かれているクエリを、そのままsql_table_nameに書くとエラーになります。

view: users_union {
  sql_table_name:
    SELECT id FROM ${users_union_no1.SQL_TABLE_NAME}
    UNION ALL
    SELECT id FROM ${users_union_no2.SQL_TABLE_NAME}
  ;;

  dimension: id {
    type: number
  }
}

しかし！！なんと、()を前後に入れてあげるだけで、エラーが出なくなります。

view: users_union {
  sql_table_name:
    (
    SELECT id FROM ${users_union_no1.SQL_TABLE_NAME}
    UNION ALL
    SELECT id FROM ${users_union_no2.SQL_TABLE_NAME}
    )
  ;;

  dimension: id {
    type: number
  }
}

なのですが、このsql_table_nameでUNIONを書く方法は非推奨です。

主な理由としては、派生テーブルの場合は永続化など拡張性がありますが、sql_table _nameの場合はこれ以上何も出来ず、ただ可読性を悪くするだけだからです。

UNIONしたい場合には、派生テーブルを使いましょう！！

最後に

いかがでしたでしょうか。

LookerでUNION処理を行おうとすると少し癖がありますが、UNIONさせたいviewの定義も、UNION処理を行うviewの定義も、基本的に派生テーブルを使うということを押さえて頂ければ問題ないかと思います！